package com.jiyinit.web.core.business.controller;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import com.jiyinit.core.framework.base.util.AESUtil;
import com.jiyinit.core.framework.base.util.Check;
import com.jiyinit.web.core.business.entity.TabCoreUserEntity;
import com.jiyinit.web.core.business.entity.TabCoreUserSalaryEntity;
import com.jiyinit.web.core.business.enums.CommonEnum;
import com.jiyinit.web.core.business.enums.UserSalaryEnum;
import com.jiyinit.web.core.business.service.UserServiceImpl;
import com.jiyinit.web.core.common.util.UserUtil;
import com.jiyinit.web.core.common.vo.CurrentUserVo;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.jiyinit.core.framework.base.entity.ResponseDto;
import com.jiyinit.web.core.business.service.UserSalaryServiceImpl;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

@Controller
@RequestMapping(value = "/userSalary")
public class UserSalaryController {

	private static Logger logger = LoggerFactory.getLogger(UserSalaryController.class);

	@Resource(name = "core.userSalaryServiceImpl")
	private UserSalaryServiceImpl userSalaryService;

    @Resource(name = "core.userServiceImpl")
    private UserServiceImpl userService;


    @RequestMapping("/toUserSalary")
    public String toList(HttpServletRequest request) {
        //往user表添加已有员工的秘钥和二次密码 二次密码默认"12345678"
//        this.add();
        return "/userSalary/userSalary";
    }
    /**
     * 解析excel文件内容
     * @author zhaolw
     * @create 2018/11/13 11:36
     * @param
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/parseWorkers", produces = "application/json; charset=utf-8")
    public ResponseEntity<ResponseDto> parseWorkers(@RequestParam(value="wordersFile",required=false)MultipartFile wordersFile){

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int totalNum = 0;//导入成功条数
        int failNum = 0;//导入失败条数
        long size = wordersFile.getSize();
        logger.info("文件大小："+size/1024+"k");

        InputStream is = null;
        Workbook wb = null;
        if((size/1024) < 2048){
            try {
                is = wordersFile.getInputStream();
                //POIFSFileSystem fs = new POIFSFileSystem(is);
                wb = WorkbookFactory.create(is);
                int num = wb.getNumberOfSheets();
                List<TabCoreUserSalaryEntity> list=new ArrayList<>();
                //遍历Sheet
                for (int i = 0; i < num; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    if(Check.isNullObj(sheet)){
                        continue;
                    }
                    int rows = sheet.getPhysicalNumberOfRows();
                    if(rows == 0){
                        continue;
                    }

                    Row row1 = sheet.getRow(0);//获取标题行
                    int cellNum = row1.getPhysicalNumberOfCells();
                    ArrayList<String> fieldList = getFieldList(row1);//获取标题对应的字段
                    if(cellNum != fieldList.size()){
                        return new ResponseEntity<>(ResponseDto.responseFail(fieldList.get(0)), HttpStatus.OK);
                    }
                    //遍历行
                    for (int j = 1; j < rows; j++) {
                        try{
                            Row row = sheet.getRow(j);
                            //获取单元格内容
                            Cell cell1 = row.getCell(0);
                            setType(row);
                            Cell cell2 = row.getCell(1);
                            Cell cell3 = row.getCell(2);
                            Cell cell4 = row.getCell(3);
                            Cell cell5 = row.getCell(4);
                            Cell cell6 = row.getCell(5);
//                            Cell cell7 = row.getCell(6);
//                            Cell cell8 = row.getCell(7);
//                            Cell cell9 = row.getCell(8);
//                            Cell cell10 = row.getCell(9);
//                            Cell cell11 = row.getCell(10);
//                            Cell cell12 = row.getCell(11);
//                            Cell cell13 = row.getCell(12);
//                            Cell cell14 = row.getCell(13);
//                            Cell cell15 = row.getCell(14);
//                            Cell cell16 = row.getCell(15);
//                            Cell cell17 = row.getCell(16);
//                            Cell cell18 = row.getCell(17);
//                            Cell cell19 = row.getCell(18);
//                            Cell cell20 = row.getCell(19);
//                            Cell cell21 = row.getCell(20);
//                            Cell cell22 = row.getCell(21);
//                            Cell cell23 = row.getCell(22);
//                            Cell cell24 = row.getCell(23);
//                            Cell cell25 = row.getCell(24);
//                            Cell cell26 = row.getCell(25);
//                            Cell cell27 = row.getCell(26);
//                            Cell cell28 = row.getCell(27);
//                            Cell cell29 = row.getCell(28);
//                            Cell cell30 = row.getCell(29);
//                            Cell cell31 = row.getCell(30);
//                            Cell cell32 = row.getCell(31);
//                            Cell cell33 = row.getCell(32);
//                            Cell cell34 = row.getCell(33);
//                            Cell cell35 = row.getCell(34);
//                            Cell cell36 = row.getCell(35);
//                            Cell cell37 = row.getCell(36);
//                            Cell cell38 = row.getCell(37);
//                            Cell cell39 = row.getCell(38);
//                            Cell cell40 = row.getCell(39);

                            if(Check.isNullStrStrict(String.valueOf(cell4))){
                                return new ResponseEntity<>(ResponseDto.responseFail(cell2+"的身份证号为空"), HttpStatus.OK);
                            }
                            TabCoreUserEntity userEntity= userService.getUserInfoByUserCertificateNo(String.valueOf(cell4));
                            if (Check.isNullObj(userEntity)){
                                return new ResponseEntity<>(ResponseDto.responseFail(cell2+"的身份证号输入有误，或此人不存在，请仔细核对后上传"), HttpStatus.OK);
                            }
                            String salarySecretKey=userEntity.getSalarySecretKey();
                            String random=AESUtil.decrypt(salarySecretKey);

                            TabCoreUserSalaryEntity entity = new TabCoreUserSalaryEntity();
                            entity.setUserCertificateNo(AESUtil.encrypt(random,String.valueOf(cell4)));
                            if(!Check.isNullStrStrict(String.valueOf(userEntity.getUserId()))){
                                entity.setUserId(userEntity.getUserId());
                            }

                            if(!Check.isNullStrStrict(String.valueOf(cell2))){
                                entity.setUserName(String.valueOf(cell2));
                            }else{
                                return new ResponseEntity<>(ResponseDto.responseFail("姓名不能为空"), HttpStatus.OK);
                            }
                            if(!Check.isNullStrStrict(String.valueOf(cell3))){
                                entity.setOrgName(String.valueOf(cell3));
                            }else{
                                entity.setOrgName("-");
                            }

                            if(!Check.isNullStrStrict(String.valueOf(cell5))){
                                entity.setSalaryMonth(AESUtil.encrypt(random, String.valueOf(cell5)));
                            }else{
                                return new ResponseEntity<>(ResponseDto.responseFail(cell2+"的工资月份不能为空"), HttpStatus.OK);
                            }
                            if(!Check.isNullStrStrict(String.valueOf(cell6))){
                                entity.setEntryDate(AESUtil.encrypt(random, String.valueOf(cell6)));
                            }else{
                                entity.setEntryDate(AESUtil.encrypt(random,"-"));
                            }
                            //自动注入
                            setFieldToEntity(fieldList, entity, random, row, 6);
//                            if(!Check.isNullStrStrict(String.valueOf(cell7))){
//                                entity.setPositionSalary(AESUtil.encrypt(random, formatStringToDouble(cell7)));
//                            }else{
//                                entity.setPositionSalary(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell8))){
//                                entity.setMeritSalary(AESUtil.encrypt(random, formatStringToDouble(cell8)));
//                            }else{
//                                entity.setMeritSalary(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell9))){
//                                entity.setPositionAllowance(AESUtil.encrypt(random,formatStringToDouble(cell9)));
//                            }else{
//                                entity.setPositionAllowance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell10))){
//                                entity.setAwardSalary(AESUtil.encrypt(random, formatStringToDouble(cell10)));
//                            }else{
//                                entity.setAwardSalary(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell11))){
//                                entity.setAgreedSalary(AESUtil.encrypt(random, formatStringToDouble(cell11)));
//                            }else{
//                                entity.setAgreedSalary(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell12))){
//                                entity.setComputerAllowance(AESUtil.encrypt(random, formatStringToDouble(cell12)));
//                            }else{
//                                entity.setComputerAllowance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell13))){
//                                entity.setMealAllowance(AESUtil.encrypt(random, formatStringToDouble(cell13)));
//                            }else{
//                                entity.setMealAllowance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell14))){
//                                entity.setRecommendAward(AESUtil.encrypt(random, formatStringToDouble(cell14)));
//                            }else{
//                                entity.setRecommendAward(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell15))){
//                                entity.setEvectionAllowance(AESUtil.encrypt(random, formatStringToDouble(cell15)));
//                            }else{
//                                entity.setEvectionAllowance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell16))){
//                                entity.setReissueSalary(AESUtil.encrypt(random, formatStringToDouble(cell16)));
//                            }else{
//                                entity.setReissueSalary(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell17))){
//                                entity.setOvertimeAllowance(AESUtil.encrypt(random, formatStringToDouble(cell17)));
//                            }else{
//                                entity.setOvertimeAllowance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell18))){
//                                entity.setLeaveDeduct(AESUtil.encrypt(random, formatStringToDouble(cell18)));
//                            }else{
//                                entity.setLeaveDeduct(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell19))){
//                                entity.setSunShine(AESUtil.encrypt(random, formatStringToDouble(cell19)));
//                            }else{
//                                entity.setSunShine(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell20))){
//                                entity.setOtherAllowance(AESUtil.encrypt(random, formatStringToDouble(cell20)));
//                            }else{
//                                entity.setOtherAllowance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell21))){
//                                entity.setTotalSalary(AESUtil.encrypt(random,formatStringToDouble(cell21)));
//                            }else{
//                                entity.setTotalSalary(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell22))){
//                                entity.setCompanyTotalInsuranceFund(AESUtil.encrypt(random, formatStringToDouble(cell22)));
//                            }else{
//                                entity.setCompanyTotalInsuranceFund(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell23))){
//                                entity.setCompanyEndowment(AESUtil.encrypt(random, formatStringToDouble(cell23)));
//                            }else{
//                                entity.setCompanyEndowment(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell24))){
//                                entity.setCompanyMedicalInsurance(AESUtil.encrypt(random, formatStringToDouble(cell24)));
//                            }else{
//                                entity.setCompanyMedicalInsurance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell25))){
//                                entity.setCompanyUnemployedInsurance(AESUtil.encrypt(random,formatStringToDouble(cell25)));
//                            }else{
//                                entity.setCompanyUnemployedInsurance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell26))){
//                                entity.setCompanyWoundInsurance(AESUtil.encrypt(random, formatStringToDouble(cell26)));
//                            }else{
//                                entity.setCompanyWoundInsurance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell27))){
//                                entity.setCompanyMaternityInsurance(AESUtil.encrypt(random, formatStringToDouble(cell27)));
//                            }else{
//                                entity.setCompanyMaternityInsurance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell28))){
//                                entity.setCompanyAccumulationFund(AESUtil.encrypt(random, formatStringToDouble(cell28)));
//                            }else{
//                                entity.setCompanyAccumulationFund(AESUtil.encrypt(random, "-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell29))){
//                                entity.setCompanyMajorDiseaseRisk(AESUtil.encrypt(random, formatStringToDouble(cell29)));
//                            }else{
//                                entity.setCompanyMajorDiseaseRisk(AESUtil.encrypt(random, "-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell30))){
//                                entity.setPersonalTotalInsuranceFund(AESUtil.encrypt(random,formatStringToDouble(cell30)));
//                            }else{
//                                entity.setPersonalTotalInsuranceFund(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell31))){
//                                entity.setPersonalEndowment(AESUtil.encrypt(random, formatStringToDouble(cell31)));
//                            }else{
//                                entity.setPersonalEndowment(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell32))){
//                                entity.setPersonalMedicalInsurance(AESUtil.encrypt(random,formatStringToDouble(cell32)));
//                            }else{
//                                entity.setPersonalMedicalInsurance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell33))){
//                                entity.setPersonalUnemployedInsurance(AESUtil.encrypt(random, formatStringToDouble(cell33)));
//                            }else{
//                                entity.setPersonalUnemployedInsurance(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell34))){
//                                entity.setPersonalAccumulationFund(AESUtil.encrypt(random, formatStringToDouble(cell34)));
//                            }else{
//                                entity.setPersonalAccumulationFund(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell35))){
//                                entity.setPersonalMajorDiseaseRisk(AESUtil.encrypt(random, formatStringToDouble(cell35)));
//                            }else{
//                                entity.setPersonalMajorDiseaseRisk(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell36))){
//                                entity.setPaidAward(AESUtil.encrypt(random, formatStringToDouble(cell36)));
//                            }else{
//                                entity.setPaidAward(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell37))){
//                                entity.setSpecialDeductionOfPersonalIncomeTax(AESUtil.encrypt(random, formatStringToDouble(cell37)));
//                            }else{
//                                entity.setSpecialDeductionOfPersonalIncomeTax(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell38))){
//                                entity.setTaxableAmount(AESUtil.encrypt(random, formatStringToDouble(cell38)));
//                            }else{
//                                entity.setTaxableAmount(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell39))){
//                                entity.setPersonalTax(AESUtil.encrypt(random,formatStringToDouble(cell39)));
//                            }else{
//                                entity.setPersonalTax(AESUtil.encrypt(random,"-"));
//                            }
//                            if(!Check.isNullStrStrict(String.valueOf(cell40))){
//                                entity.setActualPaidSalary(AESUtil.encrypt(random, formatStringToDouble(cell40)));
//                            }else{
//                                entity.setActualPaidSalary(AESUtil.encrypt(random,"-"));
//                            }

                            CurrentUserVo userVo = UserUtil.getCurrentUser();
                            Date date=new Date();
                            entity.setCreateId(userVo.getUserId());
                            entity.setLastModifyId(userVo.getUserId());
                            entity.setCreateTime(date);
                            entity.setLastModifyTime(date);
                            entity.setIsDelete(CommonEnum.IsDelete.COMMON.getCode());

                            setBlankForEntity(entity, random);//去除实体类中的空字段
                            list.add(entity);
                            totalNum++;
                        } catch (Exception e) {
                            e.printStackTrace();
                            failNum++;
                            logger.info("异常数据--------------------------");
                            continue;
                        }
                    }
                }

                int res=userSalaryService.add(list);
                if (res<1){
                    return new ResponseEntity<>(ResponseDto.responseFail("数据保存失败"), HttpStatus.OK);
                }
                logger.info("=================================导入成功！本次共导入"+totalNum+"条数据==============================================");
                return new ResponseEntity<>(ResponseDto.responseOK("导入成功！本次共导入"+totalNum+"条数据"), HttpStatus.OK);
            } catch (Exception e) {
                e.printStackTrace();
                logger.error("error: {}", e.getMessage());
                return new ResponseEntity<>(ResponseDto.responseFail("表格解析异常,上传失败"), HttpStatus.OK);
            }finally{
                try {
                    if(wb != null){
                        wb.close();
                    }
                    if(is!= null){
                        is.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }else{
            logger.info("========================================你选择的文件超过最大限制====================================================");
            return new ResponseEntity<>(ResponseDto.responseFail("文件超过最大限制"), HttpStatus.OK);
        }
    }
    public void setType(Row row){
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()){
            Cell cell = cells.next();
            if(!Check.isNullObj(cell)){
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            }
        }
    }

    /**
     * 获取表头对应的实体类字段
     * @param row
     * @return
     */
    public ArrayList<String> getFieldList(Row row){
        int numberOfCells = row.getPhysicalNumberOfCells();
        ArrayList<String> list = new ArrayList<>();
        for (int i = 0; i < numberOfCells; i++) {
            Cell cell = row.getCell(i);
            if(!Check.isNullObj(cell)){
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                String value = String.valueOf(cell);
                String fieldName = UserSalaryEnum.getFieldName(value);
                if(!Check.isNullStrStrict(fieldName)){
                    list.add(fieldName);
                }else{
                    ArrayList<String> info = new ArrayList<>();
                    info.add("第"+(i+1)+"列的\""+value+"\"没有找到对应的字段值");
                    return info;
                }
            }
        }
        return list;
    }

    /**
     * 自动注入实体类
     * @param fieldList
     * @param entity
     * @param random
     * @param row
     * @param num
     * @throws Exception
     */
    public void setFieldToEntity(ArrayList<String> fieldList, TabCoreUserSalaryEntity entity, String random, Row row, int num) throws Exception {
        Class<? extends TabCoreUserSalaryEntity> clazz = entity.getClass();
        for (int i = num; i < fieldList.size(); i++) {
            String fieldName = fieldList.get(i);
            Cell cell = row.getCell(i);
            String cellStr = String.valueOf(cell);
            Field field =  clazz.getDeclaredField(fieldName);
            field.setAccessible(true);
            if(!Check.isNullStrStrict(cellStr)){
                field.set(entity, AESUtil.encrypt(random, formatStringToDouble(cellStr)));
            }else{
                field.set(entity, AESUtil.encrypt(random,"-"));
            }

        }
    }

    /**
     * 去除实体类中的null, 用"-"代替
     * @param entity
     * @param random
     * @throws Exception
     */
    public void setBlankForEntity(TabCoreUserSalaryEntity entity, String random) throws Exception {
        Class<? extends TabCoreUserSalaryEntity> clazz = entity.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            String str = String.valueOf(fields[i].get(entity));
            String type = fields[i].getType().toString();
            if(Check.isNullStrStrict(str) && "class java.lang.String".equals(type)){
                fields[i].set(entity, AESUtil.encrypt(random, "-"));
            }
        }
    }


    /**
     * 为已有用户表添加随机秘钥和二次密码初始密码
     */
    public void add(){
        List<TabCoreUserEntity> list=userService.findUsersList();

        for (int i=0;i<list.size();i++){
            int a=(int) (Math.random()*89999999+10000000);
            String s= String.valueOf(a);
            String salarySecretKey= AESUtil.encrypt(s);
            TabCoreUserEntity tabCoreUserEntity = new TabCoreUserEntity();
            tabCoreUserEntity.setSalarySecretKey(salarySecretKey);

            String salaryPassword = AESUtil.encrypt("12345678");
            tabCoreUserEntity.setSalaryPassword(salaryPassword);
            Integer userId=list.get(i).getUserId();
            tabCoreUserEntity.setUserId(userId);
            int res=userService.updateBySalary(tabCoreUserEntity);
        }
    }

    public String formatStringToDouble(Cell cell) throws Exception {
        String str = String.valueOf(cell);
        double db = Double.parseDouble(str);
        DecimalFormat df = new DecimalFormat("0.00");
        return df.format(db);
    }

    public String formatStringToDouble(String str) throws Exception {
        double db = Double.parseDouble(str);
        DecimalFormat df = new DecimalFormat("0.00");
        return df.format(db);
    }

}
